Introduction

In this ‘code-through,’ we’ll delve into the DataTable (DT) package and its application in R for a given dataset. It’s important to note that this package is different from the data.table, an enhanced version of the data.frame. Instead, DT acts as an interface to the JavaScript library DataTables. It’s a robust tool for constructing interactive and intricate tables using HTML or Shiny. We’ll use the simple “Attitude” dataset to illustrate how even a few observations can result in creative data representation. This tutorial will demonstrate how to efficiently visualize data from 30 different departments by employing various filters and functions to conclude departments’ attitudes toward seven questions. The decision to use DT for this dataset was based on its relative simplicity and user-friendliness compared to other table packages.

Content Overview

The tutorial starts by generating a table directly from the “Attitude” dataset. Next, the dataset benefits from creating two variables: Department and Ranking. After sorting and selecting the necessary variables to achieve a complete data table, the code adds filters and x- and y-axis bars. After properly formatting all variables and removing the row_names variable, the code adds filters and other additional functions, such as colors, proportional bars, and buttons for saving and printing the table in various formats.

Why You Should Care

The DT package is a valuable tool in data analysis, particularly because of the practicality of tables. Tables are one of the most commonly used tools in data analysis, providing a straightforward way to neatly organize data. Their simplicity and logical sorting order make it easier to understand specific points or anomalies in the data, especially with proper filtering, coloring, and additional modern features such as images and objects that can contain a large amount of metadata. While plots and graphs provide great visuals, data users will mostly turn to tables to deal with specific details in the dataset.

Learning Objectives

In this tutorial, you will learn how to sort, filter, and enhance the appearance of tables. Adding features like DT extensions, colors and proportional bars can make the table more visually appealing. The tutorial will provide the code sequence for each feature. Ultimately, the final table will result from a cumulative effort to add all the features, allowing for comparison with the original table. Mastering the DT package will inspire you to explore more advanced functions within the package and other table packages in R that require more advanced coding skills.

Body Title

DT package and a practical example of utilizing filters with visible scroll, pagination, styling with selective highlights, adding proportional bars, and the ‘Button’ extension.

Further Exposition

The DT package is a gateway to the JavaScript library DataTables with a plethora of initialization options. This package seamlessly integrates data tables with JSON (JavaScript Object Notation) and its Data Tables. It’s a powerful tool that allows users to showcase data, structure tables, or matrices as interactive tables in HTML formats, primarily through the Shiny app or R Markdown. Notably, Yihui Xie is the visionary behind this package, Shiny and R Markdown, among other packages and applications.

Basic Example

A basic example shows how the table, without any additional features, can be utilized to sort the values in the columns. Sorting is achieved by clicking on individual columns’ names with numeric values. The button ‘Search’ provides some filtering options, which is particularly useful if one wants to view a factor variable, as you can see in Table 3.

# Some code

data(attitude)
datatable(
  head(attitude))

Adding new variables to the base table

In order to provide a more useful data analysis, we will create additional variables to complement the existing dataset. Two new variables, ‘Department’ and ‘ranking’ are added to the table. The ‘ranking’ variable acts as a factor variable and is composed of the values from the ‘rating’ column resulting into three new variables: ‘low’, ‘medium’, and ‘high’.

prep_table<-attitude          #Add the department names as a new column

prep_table$Department<- paste("Department", 1:nrow(prep_table)) 

prep_table<- prep_table[, c("Department", setdiff(names(prep_table),
                            "Department"))]                                                                                                          
                             #Placing Department as a first column

                                       #Creating high, medium, low ranking
rank_table <-prep_table
variable_to_rank <- prep_table$rating  #-Inf to 61 = low, 62-69 = medium, 70 to Inf = high
custom_breaks <- c(-Inf, 61, 69, Inf)  # Define custom cut points

                                       # Create the ranking variable with custom cut points
prep_table$rank_custom <- cut(variable_to_rank,
                            breaks = custom_breaks, 
                            labels = c("low", "medium", "high"),
                            include.lowest = TRUE)
prep_table <- prep_table[, c("Department", setdiff(names(prep_table), "Department"))]

final_table <- prep_table %>%             #selecting the variables of interest
select(Department,rank_custom,rating, complaints,privileges,learning,raises, 
         critical,advance)%>%
  rename('Ranking' = 'rank_custom','Rating'= 'rating','Complaints' = 'complaints',
               'Privileges' = 'privileges','Learning'='learning','Raises' = 'raises', 
               'Critical' = 'critical','Advance' ='advance') #formatting variables

datatable(
  head(final_table),  #describes the content of the table 
        caption = 'Table 2: Added Department and Ranking variables with formatting.')

Advanced Examples

In particular, enhancing the completed table with key features, such as scroll bars on the x and y axes, will be beneficial for managing large datasets. The filter buttons will display bars showing the minimum and maximum values for each column with numeric data. We removed the row names numerical range, with the Department variable serving as a unique observation for each row.

datatable(final_table,
options = list(paging = TRUE,   
pageLength = 5,                                 # number of rows to output for each page
lengthMenu = c(5,10,15,20,25,30),               # available numbers on the menu
scrollX = TRUE,                                 # enable scrolling on X axis
scrollY = TRUE,                                 # enable scrolling on Y axis
autoWidth = TRUE,                               # use smart column width handling
columnDefs = list(list(targets = '_all',        #to configure the widths of the column
className = 'dt-center'))),                     #the cell content is centered  
selection = 'single',                           # enable selection of a single row      
filter = 'top',                                 # include column filters at the top
rownames = FALSE,                               # don't show row numbers/names
caption = 'Table 3: Table with filters, x and y bars, and no row_names variable.')

What’s more, it can also be used to highlight desired categories to draw attention to specific values. In our case, we picked the’ Ranking ’Variable to define low, medium, and high values in that column with different colors.

finaltable4<-final_table
datatable(finaltable4,
  options = list(paging = TRUE,   
                 pageLength = 5,                                         
                 lengthMenu = c(5,10,15,20,25,30),                       
                 scrollX = TRUE,                                         
                 scrollY = TRUE,                                         
                 autoWidth = TRUE,                                       
                 columnDefs = list(list(targets = '_all',                
                 className = 'dt-center'))),                             
                 selection = 'single',                                         
  filter = 'top',                                                        
  rownames = FALSE,                                     #style table with colors        
  caption = 'Table 4: Table with added colors.') %>% 
  formatStyle(columns = "Ranking",background =styleEqual(c("low","medium","high"),
                                             c("#ADD8E6","#4da1bc","#4a6771")))

In addition, this table package offers the convenience of saving the table in CSV, Excel, or PDF formats, which is important for anyone who wants to work on the table in a different context. The “dom” extension arranges the elements around the table based on the specified position: ‘Bfrtip’ includes Button, filter, processing display element, table, table information summary, and pagination control. Depending on where we want the desired extension, we can choose the character ordering, which determines the layout of the elements around the table. As a significant enhancement, we added the ‘stylecolorBar’ feature for each column with numeric values, making them proportional to the values in the cells, thereby enhancing data visualization.

finaltable5<-finaltable4
#adding buttons extensions for csv,excel,pdf,and copy and print
datatable(         
  finaltable5,    
  options = list(
    paging = TRUE,
    pageLength = 10,
    lengthMenu = c(5, 10, 15, 20, 25, 30),
    scrollX = TRUE,
    scrollY = TRUE,
    autoWidth = TRUE,
    dom = 'Bfrtip',       # dom defines position of elements around the table                                                                                                                                         
    buttons = c('copy', 'csv', 'excel', 'pdf', 'print'),
    columnDefs = list(list(targets = '_all', className = 'dt-center'))
  ),
  extensions = 'Buttons',
  selection = 'single',
  filter = 'top',
  rownames = FALSE,
  caption = 'Final Table'
) %>%
                                  # Apply style for the Ranking column
  formatStyle(
    'Ranking', backgroundColor = styleEqual(
      c("low", "medium", "high"), 
      c("#ADD8E6", "#4da1bc", "#4a6771")
    )
  ) %>%
                               # Apply proportional bars to the other columns
  formatStyle(                 # Exclude the first two columns
    columns = names(finaltable5)[-c(1, 2)],  
    background = styleColorBar(range(finaltable5[,-c(1, 2)], na.rm = TRUE), color = '#FFCC99'),
    backgroundSize = '88% 38%',
    backgroundRepeat = 'no-repeat',
    backgroundPosition = 'center'
    )

Table Notes:“From a survey of the clerical employees of a large financial organization, the data are aggregated from the questionnaires of the approximately 35 employees for each of 30 (randomly selected) departments. The numbers give the percent proportion of favourable responses to seven questions in each department.” attitude: The Chatterjee–Price Attitude Data

Variable description:

                  rating----------> Overall rating
                  complaints------> Handling of employee complaint
                  privileges------> Does not allow special privileges
                  learning--------> Opportunity to learn
                  raises----------> Raises based on performance
                  critical--------> Too critical
                  advance---------> Advancement

# Further Resources

Learn more about the DT package techniques with the following:

Leah Coffin (2023)Using the Datatable DT Package

Center for Quantitative Life Sciences, Oregon State University,by gibbond (2019) R-tips: A Table Makeover with DT

Yihui Xie (2018)Package ‘DT’

More Examples on Styling Cells, Rows, and Tables

DataTables Extensions